package quanlydoanvao.baocaothang;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;

import javax.faces.model.SelectItem;

import quanlydoanra.baocao.dinhky.Month;
import quanlydoanvao.bctuan.TableDisplay;
import utils.DateUtils;
import utils.UtilsOnScopes;
import dbutil.ConnectDB;
import exportExcel.ExportExcel;

public class BeanBCThang {
		private String error;
		private String thang;
		private String nam;
		private List<TableDisplay> list;
		private String bieumau="/qldoanvao/baocaothang/emptyBieuMau.xhtml";
		private String tongsoNguoi;
		private String tongSoDoanVao;
		private String tongSoNguoiTrongDoan;
		private String pathDownload;
		private ArrayList<HashMap<String, Object>> database = new ArrayList<HashMap<String,Object>>();
		private StringBuilder dsnguoidi;
		private StringBuilder dsquoctich;
		private StringBuilder dshochieu;
		private StringBuilder dsgioitinh;
		
		
		
		public StringBuilder getDsnguoidi() {
			return dsnguoidi;
		}

		public void setDsnguoidi(StringBuilder dsnguoidi) {
			this.dsnguoidi = dsnguoidi;
		}

		public StringBuilder getDsquoctich() {
			return dsquoctich;
		}

		public void setDsquoctich(StringBuilder dsquoctich) {
			this.dsquoctich = dsquoctich;
		}

		public StringBuilder getDshochieu() {
			return dshochieu;
		}

		public void setDshochieu(StringBuilder dshochieu) {
			this.dshochieu = dshochieu;
		}

		public StringBuilder getDsgioitinh() {
			return dsgioitinh;
		}

		public void setDsgioitinh(StringBuilder dsgioitinh) {
			this.dsgioitinh = dsgioitinh;
		}

		public ArrayList<HashMap<String, Object>> getDatabase() {
			return database;
		}

		public void setDatabase(ArrayList<HashMap<String, Object>> database) {
			this.database = database;
		}

		public String getPathDownload() {
			return pathDownload;
		}

		public void setPathDownload(String pathDownload) {
			this.pathDownload = pathDownload;
		}

		/****************************************************
		 * Method name		: getTongSoNguoiTrongDoan [GETTER]
		 * Return type		: BeanBCTuan
		 * Description		: 
		 * Created date		: Jul 30, 2008
		 * Author			: Honglk
		 ****************************************************/
		public String getTongSoNguoiTrongDoan() {
			return tongSoNguoiTrongDoan;
		}

		/****************************************************
		 * Method name		: setTongSoNguoiTrongDoan [SETTER]
		 * Return type		: void
		 * Description		: 
		 * Created date		: Jul 30, 2008
		 * Author			: Honglk
		 ****************************************************/
		public void setTongSoNguoiTrongDoan(String tongSoNguoiTrongDoan) {
			this.tongSoNguoiTrongDoan = tongSoNguoiTrongDoan;
		}

		/****************************************************
		 * Method name		: getTongsoNguoi [GETTER]
		 * Return type		: BeanBCTuan
		 * Description		: 
		 * Created date		: Jul 30, 2008
		 * Author			: Honglk
		 ****************************************************/
		public String getTongsoNguoi() {
			return tongsoNguoi;
		}

		/****************************************************
		 * Method name		: setTongsoNguoi [SETTER]
		 * Return type		: void
		 * Description		: 
		 * Created date		: Jul 30, 2008
		 * Author			: Honglk
		 ****************************************************/
		public void setTongsoNguoi(String tongsoNguoi) {
			this.tongsoNguoi = tongsoNguoi;
		}

		/****************************************************
		 * Method name		: getTongSoDoanVao [GETTER]
		 * Return type		: BeanBCTuan
		 * Description		: 
		 * Created date		: Jul 30, 2008
		 * Author			: Honglk
		 ****************************************************/
		public String getTongSoDoanVao() {
			return tongSoDoanVao;
		}

		/****************************************************
		 * Method name		: setTongSoDoanVao [SETTER]
		 * Return type		: void
		 * Description		: 
		 * Created date		: Jul 30, 2008
		 * Author			: Honglk
		 ****************************************************/
		public void setTongSoDoanVao(String tongSoDoanVao) {
			this.tongSoDoanVao = tongSoDoanVao;
		}

		/****************************************************
		 * Method name		: getBieumau [GETTER]
		 * Return type		: BeanBCTuan
		 * Description		: 
		 * Created date		: Jul 30, 2008
		 * Author			: Honglk
		 ****************************************************/
		public String getBieumau() {
			return bieumau;
		}

		/****************************************************
		 * Method name		: setBieumau [SETTER]
		 * Return type		: void
		 * Description		: 
		 * Created date		: Jul 30, 2008
		 * Author			: Honglk
		 ****************************************************/
		public void setBieumau(String bieumau) {
			this.bieumau = bieumau;
		}

		/****************************************************
		 * Method name		: getList [GETTER]
		 * Return type		: BeanBCTuan
		 * Description		: 
		 * Created date		: Jul 29, 2008
		 * Author			: Honglk
		 ****************************************************/
		public List<TableDisplay> getList() {
			return list;
		}

		/****************************************************
		 * Method name		: setList [SETTER]
		 * Return type		: void
		 * Description		: 
		 * Created date		: Jul 29, 2008
		 * Author			: Honglk
		 ****************************************************/
		public void setList(List<TableDisplay> list) {
			this.list = list;
		}

		/****************************************************
		 * Method name		: getError [GETTER]
		 * Return type		: BeanBCTuan
		 * Description		: 
		 * Created date		: Jul 29, 2008
		 * Author			: Honglk
		 ****************************************************/
		public String getError() {
			return error;
		}

		/****************************************************
		 * Method name		: setError [SETTER]
		 * Return type		: void
		 * Description		: 
		 * Created date		: Jul 29, 2008
		 * Author			: Honglk
		 ****************************************************/
		public void setError(String error) {
			this.error = error;
		}

		

		/***********
		 * Method name		: getThang [GETTER]
		 * Return type		: BeanBCTuan
		 * Description		: 
		 * Created date		: Jul 29, 2008
		 * Author			: Honglk
		 ****************************************************/
		public String getThang() {
			return thang;
		}

		/****************************************************
		 * Method name		: setThang [SETTER]
		 * Return type		: void
		 * Description		: 
		 * Created date		: Jul 29, 2008
		 * Author			: Honglk
		 ****************************************************/
		public void setThang(String thang) {
			this.thang = thang;
		}

		/****************************************************
		 * Method name		: getNam [GETTER]
		 * Return type		: BeanBCTuan
		 * Description		: 
		 * Created date		: Jul 29, 2008
		 * Author			: Honglk
		 ****************************************************/
		public String getNam() {
			return nam;
		}

		/****************************************************
		 * Method name		: setNam [SETTER]
		 * Return type		: void
		 * Description		: 
		 * Created date		: Jul 29, 2008
		 * Author			: Honglk
		 ****************************************************/
		public void setNam(String nam) {
			this.nam = nam;
		}

		/**************************************
		 * Method name		: getMonth
		 * Return type		: List<Year>
		 * Decription		:Lay ra danh sach cac thang
		 * Created date		: Jul 29, 2008
		 * Author			: Honglk
		 **************************************/
		
		private List<Month> getMonth() {
			List<Month> list = new ArrayList<Month>();
			int beginMonth = 1;
			while (beginMonth <= 12) {
				Month month = new Month();
				month.setValue(String.valueOf(beginMonth));
				month.setLable(String.valueOf("Tháng "+beginMonth));
				list.add(month);
				beginMonth += 1;
			}return list;
		}
		
		/**************************************
		 * Method name		: listItemMonth
		 * Return type		: SelectItem[]
		 * Decription		:Lay ra cac item select cua thang
		 * Created date		: Jul 13, 2008
		 * Author			: Honglk
		 **************************************/
		public SelectItem[] listItemMonth() {
			List<Month> li = this.getMonth();
			SelectItem[] arrItem = new SelectItem[li.size() + 1];
			arrItem[0] = new SelectItem("-1", "---Chọn---");
			for (int i = 0; i < li.size(); i++) {
				Month month = li.get(i);
				arrItem[i + 1] = new SelectItem(month.getValue(), month.getLable());
			}
			return arrItem;
		}
		
		public SelectItem[] getListItemMonth() {
			return this.listItemMonth();
		}
		
		private String filtData(String nam,String thang){
			String result ="";
			int dkyInt = Integer.parseInt(thang);
			switch (dkyInt) {
			case 1:
				result = "DATE_PART('month',A.ngayden)= '1' " +
					    " AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 2:
				result = " DATE_PART('month',A.ngayden)= '2' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 3:
				result = " DATE_PART('month',A.ngayden)= '3' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 4:
				result = " DATE_PART('month',A.ngayden)= '4' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 5:
				result = "DATE_PART('month',A.ngayden)= '5' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 6:
				result = " DATE_PART('month',A.ngayden)= '6' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 7:
				result = " DATE_PART('month',A.ngayden)= '7' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 8:
				result = " DATE_PART('month',A.ngayden)= '8' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 9:
				result = "DATE_PART('month',A.ngayden)= '9' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 10:
				result = " DATE_PART('month',A.ngayden)= '10' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 11:
				result = " DATE_PART('month',A.ngayden)= '11' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			case 12:
				result = " DATE_PART('month',A.ngayden)= '12' " +
				" AND DATE_PART('year',A.ngayden)= '" +nam +"'";
				break;
			default:
				break;
			}
			return result;
		}
		
		public String tables() {
			List<TableDisplay> list = new ArrayList<TableDisplay>();
			String cond=this.filtData(this.nam, this.thang);
			String SQL = "select DISTINCT E.sovb,E.coquandenghi,E.ngayxuatvb,E.noidung,A.diadiemlamviec," +
					" A.ngayden,C.linhvuc,D.soubndtinh,D.ngayphathanh,A.madoan " +
						  " from  doanvao A inner join dsnguoiden B on A.madoan =B.iddoanvao " +
			              " inner join mucdich C on A.idmucdich = C.id " +
			              " inner join vanbannhan D on A.idvbvao = D.id " +
			              " inner join vanbanden E on E.id = A.idvbden " +
			              " WHERE " + cond;
			System.out.println(SQL);
			ArrayList<HashMap<String, Object>> array = ConnectDB
					.getDataTypeList(SQL);
			
		  // danh cho bao cao(Xuat ra file Excel)
			this.setTongSoDoanVao("<b>Tổng số đoàn vào: "+String.valueOf(array.size() +"</b>"));
			for (HashMap<String, Object> row : array) {
				this.getMemberList((String) row.get("madoan"));
				row.put("nguoitrongdoan", this.getDsnguoidi().toString());
				row.put("dssohc", this.getDshochieu().toString());
				row.put("dsquoctich", this.getDsquoctich().toString());
				row.put("dsgioitinh", this.getDsgioitinh().toString());
				
				row.put("slthanhvientrongdoan", this.tongSoNguoiTrongDoan(row.get("madoan").toString()));
				String ngayxuatvb = (row.get("ngayxuatvb")== null?"":row.get("ngayxuatvb").toString());
				row.put("ngayxuatvanban", DateUtils.formatDate(ngayxuatvb));
				String ngayden = (row.get("ngayden")== null?"":row.get("ngayden").toString());
				row.put("ngaydenlamviec", DateUtils.formatDate(ngayden));
				String ngayphatvb = (row.get("ngayphathanh")== null?"":row.get("ngayphathanh").toString());
				row.put("ngayphathanhvb", DateUtils.formatDate(ngayphatvb));
			}
			this.database = array;
			int intTongNguoi = 0;
			try {
				if (array.size() > 0) {
					HashMap<String, Object> hash = new HashMap<String, Object>();
					int stt = 0;
					for (int i = 0; i < array.size(); i++) {
						hash = (HashMap<String, Object>) array.get(i);
						intTongNguoi += Integer.parseInt(this.tongSoNguoiTrongDoan(hash.get("madoan").toString()));
						stt = i + 1;
						TableDisplay table = new TableDisplay();
						table.setStt(String.valueOf(stt));
						table.setSovb(hash.get("sovb")==null?"":hash.get("sovb").toString());
						table.setCoquandenghi(hash.get("coquandenghi")== null?"":hash.get("coquandenghi").toString());
						String ngayxuatvb = (hash.get("ngayxuatvb")== null?"":hash.get("ngayxuatvb").toString());
						table.setNgayxuatvb(DateUtils.formatDate(ngayxuatvb));
						table.setNoidung(hash.get("noidung")==null?"":hash.get("noidung").toString());
						table.setDiadiemlamviec(hash.get("diadiemlamviec")==null?"":hash.get("diadiemlamviec").toString());
						String ngayden = (hash.get("ngayden")== null?"":hash.get("ngayden").toString());
						table.setNgayden(DateUtils.formatDate(ngayden));
						table.setLinhvuc(hash.get("linhvuc")==null?"":hash.get("linhvuc").toString());
						table.setSoubndtinh(hash.get("soubndtinh")==null?"":hash.get("soubndtinh").toString());
						String ngayphatvb = (hash.get("ngayphathanh")== null?"":hash.get("ngayphathanh").toString());
						table.setNgayphathanh(DateUtils.formatDate(ngayphatvb));
						table.setSohc(hash.get("dssohc")==null?"":hash.get("dssohc").toString().replaceAll(System.getProperty("line.separator"),"<br/>"));
						table.setDsThanhVien((hash.get("nguoitrongdoan").toString()).replaceAll(System.getProperty("line.separator"),"<br/>"));
						table.setQuoctich(hash.get("dsquoctich")==null?"":hash.get("dsquoctich").toString().replaceAll(System.getProperty("line.separator"),"<br/>"));
						table.setGioitinh(hash.get("dsgioitinh")==null?"":hash.get("dsgioitinh").toString().replaceAll(System.getProperty("line.separator"),"<br/>"));
						table.setSlThanhVienTrongDoan(this.tongSoNguoiTrongDoan(hash.get("madoan").toString()));
						list.add(table);
					}
				}else{
					list = new ArrayList<TableDisplay>();
					this.setError("Không có dữ liệu !");
				}
				String strTongNguoi = "<b>Tổng số: " +String.valueOf(intTongNguoi) +" Người</b>";
				this.setTongsoNguoi(strTongNguoi);
				this.setList(list);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return "";
		}
		
		public String view(){
			this.setError("");
			this.tables();
			if (this.checkError() == false) {
				return "";
			} else if (this.getList() != null && this.getList().size() > 0) {
				this.setBieumau("/qldoanvao/baocaothang/bieumau15cot.xhtml");
			}
			return "okBaoCaoThang";
		}
		
		public boolean checkError() {
			 if (this.thang == null || this.thang.equals("-1")) {
				this.setError("Bạn chưa chọn tháng !");
				return false;
			} else if (this.nam == null || this.nam.equals("-1")) {
				this.setError("Bạn chưa chọn năm !");
				return false;
			}

			return true;
		}
		
		private void getMemberList(String idDoanVao) {
			try {
				String SQL = "SELECT DISTINCT A.ten,A.quoctich, A.sohc,A.gioitinh  FROM dsnguoiden A, doanvao B WHERE A.iddoanvao = B.madoan AND B.madoan = '" + idDoanVao+"'";
				ArrayList<HashMap<String, Object>> arr = ConnectDB.getDataTypeList(SQL);
				if(arr.size() > 0){
					StringBuilder dsnguoidi = new StringBuilder();
					StringBuilder dsquoctich = new StringBuilder();
					StringBuilder dshochieu = new StringBuilder();
					StringBuilder dsgioitinh = new StringBuilder();
					for(int i = 0; i < arr.size(); i++){
						HashMap<String, Object> row = arr.get(i);
						String quotich = row.get("quoctich") == null?"":((String)row.get("quoctich")).trim();
						String hochieu = row.get("sohc") == null?"":((String)row.get("sohc")).trim();
						String gioitinh = row.get("gioitinh") == null?"":((String)row.get("gioitinh")).trim();
						String nguoidi = row.get("ten") == null?"":((String)row.get("ten")).trim();
						
						dsnguoidi.append( nguoidi+ System.getProperty("line.separator"));
						dsquoctich.append(quotich + System.getProperty("line.separator"));
						dshochieu.append(hochieu + System.getProperty("line.separator"));
						dsgioitinh.append(gioitinh + System.getProperty("line.separator"));
					}
					this.setDsgioitinh(dsgioitinh);
					this.setDshochieu(dshochieu);
					this.setDsnguoidi(dsnguoidi);
					this.setDsquoctich(dsquoctich);
				} else {
					return ;
				}

			} catch (Exception e) {
				this.setError("Không thể lấy danh sách người trong đoàn.");
			}
		}
		
		public String tongSoNguoiTrongDoan(String idDoanvao){
			String result ="0";
			String SQL ="SELECT DISTINCT COUNT(*) AS count FROM dsnguoiden B " +
			" WHERE  B.iddoanvao='"+idDoanvao+"'";
			System.out.println(SQL);
			ArrayList<HashMap<String, Object>> arr = ConnectDB.getDataTypeList(SQL);
			if(arr.size() > 0){
				HashMap<String, Object> hash = arr.get(0);
				result = hash.get("count").toString();
			}
			return result;
		}
		
		
		public String reset() {
			UtilsOnScopes.getSession().removeAttribute("beanBCThang");
			return "okBaoCaoThang";
		}
		
		/**************************************
		 * Method name		: export
		 * Return type		: String
		 * Decription		:Xuat ra file Excel
		 * Created date		: Jul 26, 2008
		 * Author			: Honglk
		 * Modified			: Phonglt [sua pathDownload]
		 **************************************/
		public String export() {
			this.setError("");
			if(this.checkError()== false){
				return "";
			}
			this.tables();
			//this.database = this.convertListToHashtable();
			if(this.database != null && this.database.size() > 0){
				//Header
			Hashtable<String, String> heading = new Hashtable<String, String>();	
				heading.put("THANG", this.thang);
				heading.put("NAM", this.nam);
				//Footer
				Hashtable<String, String> ending = new Hashtable<String, String>();	
				ending.put("TONGSODOAN",String.valueOf(this.database.size()));
				ending.put("NGAY", String.valueOf(DateUtils.LayNgayHienTai()));
				ending.put("THANG", String.valueOf(DateUtils.LayThangHienTai()));
				ending.put("NAM", String.valueOf(DateUtils.LayNamHienTai()));
			ExportExcel.exportJExcel_HaveGroup("quanlydoanvao_template", "baocaothang.xml",
					"exp_quanlydoanvao", this.database, UtilsOnScopes.getRequest(), 
					heading, ending);
			this.setPathDownload( ExportExcel.pathDownload);
			this.setBieumau("/qldoanvao/baocaothang/viewExportExcel.xhtml");
			}
			else{
				this.setError("Không có dữ liệu !");
			}
			return "okBaoCaoThang";
		}
	}
